﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MaxLeaf.Platform.Contract;
using MaxLeaf.Platform.DataContract;
using CommonsLib.DB;
using System.Transactions;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Data.Common;
using System.Diagnostics;
using CommonsLib.Settings;
using CommonsLib.Utility;
using System.Data;

using System.Security.Cryptography;
using MaxLeaf.Platform.Functions;
using MaxLeaf.Platform.Commons;

namespace MaxLeaf.Platform.DALImplementation
{
    public class mCompanyDAL : BaseDAL
    {
        public override string IgnoreUpdateFields
        {
            get
            {
                return "WebUserID";
            }
        }
        public static int GetParentCompanyID(DBAccess dbcon ,int intCompany)
        {
            string strSQL="SELECT ParentID from mCompany where CompanyID=@CompanyID";
            int intReturn = CvtFunc.ToInt ( dbcon.GetValue ( strSQL, new SqlParameter[]{ new SqlParameter ( "@CompanyID", intCompany.ToString())}));
            return intReturn;
        }

        public  bool InitData(DBAccess dbcon, int intFromCompanyID, int intToCompanyID)
        {
            string strSQL = "exec P_InitCompanyData @FromCompanyID,@ToCompanyID";
            dbcon.GetValue(strSQL, new SqlParameter[] { new SqlParameter("@FromCompanyID", intFromCompanyID.ToString()),new SqlParameter("@ToCompanyID", intToCompanyID.ToString()) });

            return true;
        }

        public string GetUserSN(DBAccess dbcon, string  strUserSNSt)
        {
            string strSQL = string.Format("select max(user_sn) from user_info where user_sn like'{0}______'",strUserSNSt);
            return dbcon.GetValue(strSQL);

            
        }

        public bool Delete_Web(DBAccess dbcon, object model)
        {
            using (TransactionScope tx = DataFunc.CreateTransactionScope(TransactionScopeOption.Suppress))
            {
                using (DBAccess dbWebCon = new DBAccess(DataFunc.GetConnectionInfo(Const.DB_WebWrite)))
                {
                    mCompanyDataModel mCompany = model as mCompanyDataModel;
                    string strSQL = "Update user_main set Is_Delete =1 where user_id=@user_id";
                    dbWebCon.ExecuteSql(strSQL, new MySqlParameter[] { new MySqlParameter("@WebUserID", mCompany.WebUserID) });
                }
                return true;
            }
            
        }

        public bool Update_Web(DBAccess dbcon, object model, string strUserSN)
        {            
            using (TransactionScope tx = DataFunc.CreateTransactionScope(TransactionScopeOption.Suppress))
            {
                using (DBAccess dbWebCon = new DBAccess(DataFunc.GetConnectionInfo(Const.DB_WebWrite)))
                {
                    mCompanyDataModel mCompany = model as mCompanyDataModel;
                    if (mCompany.WebUserID == 0)
                    {
                        mCompany.WebUserID = CvtFunc.ToInt(dbcon.GetValue ("select webuserid from mCompany where companyid='"+ mCompany.Key +"'"));
                    }
                    string strSQL = @"select * from user_main where user_id =" + mCompany.WebUserID + "";
                    DbDataReader drRow = dbWebCon.GetDataFirstRow(strSQL);
                    if (!drRow.Read()) // 新增
                    {
                        strSQL = "select * from mCompany_Info where companyID=@companyID";
                        DataTable dtInfo = dbcon.GetDataTable(strSQL, new SqlParameter[] { new SqlParameter("@companyID", mCompany.Key) });
                        
                        if (dtInfo.Rows.Count == 0)
                        {
                            return false; //没有填写mcompany_info就直接跳出
                        }
                        else 
                        {
                            drRow.Close();  
                            DataRow drInfo = dtInfo.Rows[0];                                                    
                            strSQL = @"INSERT INTO user_main (Company_Name,Mobile,eMail,register_time,password,mobile_active,active)
                                               VALUES (@Company_Name, @Mobile, @eMail,@register_time,@PassWord,@mobile_active,@active)";
                            dbWebCon.ExecuteSql(strSQL, new MySqlParameter[] { 
                        new MySqlParameter("@Company_Name", mCompany.Company_Name),                        
                        new MySqlParameter("@Mobile", mCompany.Mobile) ,
                        new MySqlParameter("@eMail", mCompany.eMail) ,
                        new MySqlParameter("@Fax", mCompany.Fax) ,
                        new MySqlParameter("@register_time", CvtFunc.ToUnitStamp( DateTime.Now)) ,
                        new MySqlParameter("@PassWord", mCompany.PassWord) ,
                        new MySqlParameter("@mobile_active", mCompany.Mobile_Active) ,
                        new MySqlParameter("@active", mCompany.Active) 
                        });

                            string strUser_ID = CvtFunc.ToString(dbWebCon.GetValue("Select user_id from user_main where Company_Name='"+ mCompany.Company_Name +"' order by user_id DESC LIMIT  1"));
                            if (strUser_ID == "0") return false; //没有填加成功。

                            strSQL = "UPDATE mCompany set WebUserID='" + strUser_ID + "' where companyid='" + mCompany.Key + "'";
                            dbcon.ExecuteSql(strSQL);

                            strSQL = "UPDATE mUser_Main Set WebUserID='"+ strUser_ID +"' where companyid='"+ mCompany.Key +"' and is_companyuser=1";
                            dbcon.ExecuteSql(strSQL);
                           
                            strSQL = @"INSERT INTO user_info (User_ID,User_SN,company_province,company_city,company_region,company_address,company_zip,company_tel,contact,parent_id,is_contract,
                                                          tax_type,tax_head,tax_company_address,tax_company_tel,tax_bank_name,tax_bank_accounts)
                                  VALUES (@User_ID,@User_SN,@company_province,@company_city,@company_region,@company_address,@company_zip,@company_tel,@contact,@parent_id,@is_contract,
                                          @tax_type,@tax_head,@tax_company_address,@tax_company_tel,@tax_bank_name,@tax_bank_accounts)";

                            string strWebUserID = "0";
                            //还要加总公司在网站上的ID，user_info.ParentID
                            int intParentCompanyID = GetParentCompanyID(dbcon, mCompany.Key);
                            if (intParentCompanyID > 0)
                            {
                                strWebUserID = CvtFunc.ToString(dbcon.GetValue("select WebUserID from mCompany where companyID=" + intParentCompanyID + ""));
                                //if (strWebUserID == "0") //没有总公司                        
                            }
                            if (CvtFunc.ToBoolean(drInfo["Is_Buyer"]) && CvtFunc.ToBoolean(drInfo["Is_SeparateAccount"]))
                            {
                                strWebUserID = "0";
                            }
                            else
                            {
                                strWebUserID = CvtFunc.ToString(dbcon.GetValue("SELECT B.WebUserID from mCompany B inner join (select * from mCompany where CompanyID='" + drInfo["CompanyID"] + "') A on a.parentid=b.CompanyID "));
                            }
                            if (strWebUserID == "") strWebUserID = "0";
                            dbWebCon.ExecuteSql(strSQL, new MySqlParameter[] { 
                                                    new MySqlParameter("@User_ID", strUser_ID),  
                                                    new MySqlParameter("@User_SN", strUserSN),
                                                    new MySqlParameter("@company_province", CvtFunc.ToString( drInfo["ProvID"]) ) ,
                                                    new MySqlParameter("@company_city",  CvtFunc.ToString( drInfo["CityID"])) ,
                                                    new MySqlParameter("@company_region", CvtFunc.ToString( drInfo["RegionID"])) ,
                                                    new MySqlParameter("@company_address", mCompany.Address) ,
                                                    new MySqlParameter("@company_zip", mCompany.PostalCode) ,
                                                    new MySqlParameter("@company_tel", mCompany.Mobile) ,
                                                    new MySqlParameter("@contact", mCompany.Contact),
                                                    new MySqlParameter("@parent_id", strWebUserID),
                                                    new MySqlParameter("@is_contract","1" ),
                                                    new MySqlParameter("@tax_type", CvtFunc.ToString(drInfo["invoicetype"])),
                                                    new MySqlParameter("@tax_head", CvtFunc.ToString(drInfo["invoicehead"])),
                                                    new MySqlParameter("@tax_company_address", CvtFunc.ToString(drInfo["invoiceaddress"])),
                                                    new MySqlParameter("@tax_company_tel", CvtFunc.ToString(drInfo["invoicephone"])),
                                                    new MySqlParameter("@tax_bank_name", CvtFunc.ToString(drInfo["invoicebankname"])),
                                                    new MySqlParameter("@tax_bank_accounts", CvtFunc.ToString(drInfo["invoicebankacct"]))                                                    
                                                    });

                            
                        }
                    }
                    else
                    {
                        drRow.Close(); 
                        strSQL = @"UPDATE user_main Set Company_Name=@Company_Name, Mobile=@Mobile, eMail=@eMail                                                 
                                  where user_id=@user_id";
                        dbWebCon.ExecuteSql(strSQL, new MySqlParameter[] { 
                    new MySqlParameter("@user_id", mCompany.WebUserID),
                    new MySqlParameter("@Mobile", mCompany.Mobile) ,
                    new MySqlParameter("@eMail", mCompany.eMail) ,
                    new MySqlParameter("@Fax", mCompany.Fax)  ,
                    new MySqlParameter("@Company_Name", mCompany.Company_Name)  
                    });


                        strSQL = "select * from mCompany_Info where companyID=@companyID";
                        using (DbDataReader drInfo = dbcon.GetDataFirstRow(strSQL, new SqlParameter[] { new SqlParameter("@companyID", mCompany.Key) }))
                        {
                            if (!drInfo.Read()) return true; //没有填写mcompany_info就直接跳出

                            int intParent_ID = 0;
                            if (CvtFunc.ToBoolean(drInfo["Is_Buyer"]) && CvtFunc.ToBoolean(drInfo["Is_SeparateAccount"]))
                            {
                                intParent_ID = 0;
                            }
                            else
                            {
                                intParent_ID = CvtFunc.ToInt(dbcon.GetValue("SELECT B.WebUserID from mCompany B inner join (select * from mCompany where CompanyID='" + drInfo["CompanyID"] + "') A on a.parentid=b.CompanyID "));
                            }

                            strSQL = @"UPDATE user_info set company_address=@company_address,company_zip=@company_zip,company_tel=@company_tel,contact=@contact,
                                                         tax_type=@tax_type,tax_head=@tax_head,tax_company_address=@tax_company_address,
                                                         tax_company_tel=@tax_company_tel,tax_bank_name=@tax_bank_name,tax_bank_accounts=@tax_bank_accounts,parent_id=@parent_id                             
                                    where User_ID=@WebUserID";
                            dbWebCon.ExecuteSql(strSQL, new MySqlParameter[] {                                                   
                                                    new MySqlParameter("@company_address", mCompany.Address) ,
                                                    new MySqlParameter("@company_zip", mCompany.PostalCode) ,
                                                    new MySqlParameter("@company_tel", mCompany.Mobile) ,
                                                    new MySqlParameter("@contact", mCompany.Contact),     
                                                    new MySqlParameter("@tax_type", CvtFunc.ToString(drInfo["InvoiceType"])),                   
                                                    new MySqlParameter("@tax_head", CvtFunc.ToString(drInfo["invoicehead"])),
                                                    new MySqlParameter("@tax_company_address", CvtFunc.ToString(drInfo["invoiceaddress"])),
                                                    new MySqlParameter("@tax_company_tel", CvtFunc.ToString(drInfo["invoicephone"])),
                                                    new MySqlParameter("@tax_bank_name", CvtFunc.ToString(drInfo["invoicebankname"])),
                                                    new MySqlParameter("@tax_bank_accounts", CvtFunc.ToString(drInfo["invoicebankacct"])),
                                                    new MySqlParameter("@WebUserID", mCompany.WebUserID),   
                                                    new MySqlParameter("@parent_id", intParent_ID) 
                                                    });

                        }
                    }
                }
                return true;
            }
        }
    }
}
